package com.sanalkitaplik.hibernateutil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import org.hibernate.Query;
import org.hibernate.Session;
import com.sanalkitaplik.packageclassdto.AdminInfoDTO;
import com.sanalkitaplik.packageclassdto.AdminLoginInfoDTO;

public class AdminsUtil {

	private static Connection connection = null;
	private static Statement stmt = null;
	private static ResultSet rs = null;
	private static Session session = null;
	
	/* Check işlemi doğruysa eğer FALSE döndürecek. */
	public static boolean loginCheck(String username, String admin_pass){
		try{
			session = SessionFactoryBuilder.getSessionFactory().openSession(); 
			
			Query query = session.createSQLQuery("select ADMIN_USERNAME, ADMIN_PASSWORD " +
										"from SNLKTPLK_ADMIN_LOGIN_INFO " +
										"where ADMIN_USERNAME = '"+username+"' and ADMIN_PASSWORD = '"+admin_pass+"'");
			
			if(!query.list().isEmpty())
				return false;
			return true;
		}
		catch(Exception ex){
			System.out.println("An exception occured while searching : " +ex.getMessage());
			return true;
		}
		finally{
			session.flush();
			session.close();
		}
	}
	
	public static boolean adminRegister(AdminInfoDTO infoDto, AdminLoginInfoDTO logInfoDto, String group_name) throws SQLException{
			Connection connection = Connect.getConnection();
	    	Statement stmt = connection.createStatement();
	    	
	    	ResultSet rs = stmt.executeQuery("select count(ADMIN_USERNAME) from SNLKTPLK_ADMIN_LOGIN_INFO where ADMIN_USERNAME = '" +
	    			logInfoDto.getAdmin_username() + "'");

	    	if (rs.next()) {
	    		if (rs.getInt(1) > 0)
	    			return false;
	    	}
	    	
	    	rs = stmt.executeQuery("select GROUP_ID from SNLKTPLK_ADMIN_AUTHORIZATION_GROUPS where GROUP_NAME='"+group_name+"'");
	    	long group_id = 0;
	    	if(rs.next())
	    		group_id=rs.getLong("GROUP_ID");
	    	
			String sql = "insert into SNLKTPLK_ADMIN_LOGIN_INFO values (default,'"+logInfoDto.getAdmin_username()+"','"+logInfoDto.getAdmin_password()+"','"+logInfoDto.getAdmin_email()+"')";
			
			String sql2 = "insert into SNLKTPLK_ADMIN_INFO values (" +
					"(select ADMIN_ID from SNLKTPLK_ADMIN_LOGIN_INFO where ADMIN_USERNAME='"+logInfoDto.getAdmin_username()+"')," +
							"'"+infoDto.getLast_name()+"'," +
									""+group_id+"," +
											"'"+infoDto.getFirst_name()+"')";
			
			stmt.executeUpdate(sql);
			stmt.executeUpdate(sql2);
			
			connection.close();
			stmt.close();
			rs.close();
			
			return true;
	}
	
	public static ArrayList<String> getAdminUsernames() throws SQLException{
		connection = Connect.getConnection();
		stmt = connection.createStatement();
		rs = stmt.executeQuery("select ADMIN_USERNAME from SNLKTPLK_ADMIN_LOGIN_INFO");
		
		ArrayList<String> list = new ArrayList<String>(rs.getRow());
		while(rs.next()){
			list.add(rs.getString("ADMIN_USERNAME"));
		}
		
		return list;
	}
	
	public static HashMap<String, Object> getAdminInfo(String username) throws SQLException{
		connection = Connect.getConnection();
		stmt = connection.createStatement();
		rs = stmt.executeQuery("select * from SNLKTPLK_ADMIN_INFO as ai join SNLKTPLK_ADMIN_LOGIN_INFO as ali " +
				"on ai.ADMIN_ID=ali.ADMIN_ID where ali.ADMIN_USERNAME = '"+username+"'");
		
		HashMap<String, Object> map = new HashMap <String, Object>();
		long group_id = 0;
		
		while(rs.next()){
			map.put("groupid", rs.getLong("ADMIN_ID"));
			map.put("username", rs.getString("ADMIN_USERNAME"));
			map.put("password", rs.getString("ADMIN_PASSWORD"));
			map.put("email", rs.getString("ADMIN_EMAIL"));
			map.put("firstname", rs.getString("FIRST_NAME"));
			map.put("lastname", rs.getString("LAST_NAME"));
			group_id = rs.getLong("GROUP_ID");
		}
		rs = stmt.executeQuery("select GROUP_NAME from SNLKTPLK_ADMIN_AUTHORIZATION_GROUPS where GROUP_ID='"+group_id+"'");
		if(rs.next())
			map.put("groupname", rs.getString("GROUP_NAME"));
		
		return map;
	}
}
